﻿using System;
using System.Collections;
using System.Data;
using System.Data.Common;

namespace app.scrum
{
    public interface IScrumObject
    {
        ArrayList read();
        int create(ArrayList sObjList);
        int update(ArrayList sObjList);
        int delete(ArrayList sObjList);
        //int create(IScrumObject sObj);
        //int update(IScrumObject sObj);
        //int delete(IScrumObject sObj);
    }

    public class ScrumObject : IScrumObject
    {
        public string tableName;
        public string readCmdText;
        public string updateCmdText;
        public string createCmdText;
        public string deleteCmdText;
        public ArrayList fieldList = new ArrayList();
        public Hashtable fieldMap = new Hashtable();
        public ArrayList childList = new ArrayList();
        public Hashtable childMap = new Hashtable();

        public static string objectName_Product = "product__c";
        public static string objectName_Release = "release__c";
        public static string objectName_Sprint = "sprint__c";
        public static string objectName_Backlog = "backlog__c";
        public static string objectName_Task = "task__c";
        public static string objectName_Team = "team__c";
        public static string objectName_User = "user__c";
        public static string objectName_Settings = "settings__c";

        public virtual ArrayList read()
        {
            return readByReader();
        }

        public virtual ArrayList readByAdaper()
        {
            DbConnection con = null;
            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            ArrayList sObjList = null;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                if (Util.isEmpty(this.readCmdText))
                {
                    this.genReadCmdText();
                }
                cmd.CommandText = this.readCmdText;
                adapter = DatabaseManager.createDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds, tableName);
                DataTable table = ds.Tables[tableName];
                int rowCount = table.Rows.Count;
                int fieldCount = table.Columns.Count;
                sObjList = new ArrayList();
                for (int i = 0; i < rowCount; i++)
                {
                    ScrumObject sObj = new ScrumObject();
                    for (int j = 0; j < fieldCount; j++)
                    {
                        ScrumField field = new ScrumField();
                        field.name = table.Columns[j].ColumnName;
                        field.value = table.Rows[i][field.name];
                        field.type = table.Columns[j].DataType;
                        field.typeName = table.Columns[j].DataType.FullName;
                        this.addField(sObj,field);
                    }
                    ScrumField objTypeField = new ScrumField();
                    objTypeField.name = ScrumField.fieldName_ObjectType;
                    objTypeField.value = tableName;
                    this.addField(sObj, objTypeField);
                    sObjList.Add(sObj);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd, adapter);
            }
            return sObjList;
        }

        public virtual ArrayList readByReader()
        {
            DbConnection con = null;
            DbCommand cmd = null;
            DbDataReader reader = null;
            ArrayList sObjList = null;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                if (Util.isEmpty(this.readCmdText))
                {
                    this.genReadCmdText();
                }
                cmd.CommandText = this.readCmdText;
                reader = cmd.ExecuteReader();
                int fieldCount = reader.FieldCount;
                sObjList = new ArrayList();
                while (reader.Read())
                {
                    ScrumObject sObj = new ScrumObject();
                    for (int j = 0; j < fieldCount; j++)
                    {
                        ScrumField field = new ScrumField();
                        field.name = reader.GetName(j);
                        field.value = reader.GetValue(j);
                        field.type = reader.GetFieldType(j);
                        field.typeName = reader.GetDataTypeName(j);
                        this.addField(sObj, field);
                    }
                    ScrumField objTypeField = new ScrumField();
                    objTypeField.name = ScrumField.fieldName_ObjectType;
                    objTypeField.value = tableName;
                    this.addField(sObj, objTypeField);
                    sObjList.Add(sObj);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd, reader);
            }
            return sObjList;
        }

        public virtual ArrayList getScrumObjectSimpleList(string objName)
        {
            ScrumObject sObj = new ScrumObject();
            sObj.tableName = objName;
            sObj.readCmdText = "select " + ScrumField.fieldName_Id + ", " + ScrumField.fieldName_Name + " from " + objName;
            ArrayList list = sObj.read();
            return list;
        }

        public virtual long getMaxId()
        {
            DbConnection con = null;
            DbCommand cmd = null;
            DbDataReader reader = null;
            long maxId = 0;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                cmd.CommandText = "select max(id__c) from " + tableName;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    maxId = reader.GetInt64(0);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd, reader);
            }
            return maxId;
        }

        public virtual int create(ArrayList sObjList)
        {
            int num = 0;
            DbConnection con = null;
            DbCommand cmd = null;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                cmd.CommandText = this.createCmdText;
                num = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd);
            }
            return num;
        }

        public virtual int create(ScrumObject sObj)
        {
            int num = 0;
            DbConnection con = null;
            DbCommand cmd = null;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                if (Util.isEmpty(this.createCmdText))
                {
                    this.genCreateCmdText(sObj);
                }
                cmd.CommandText = this.createCmdText;
                num = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd);
            }
            return num;
        }

        public virtual int update(ArrayList sObjList)
        {
            int num = 0;
            DbConnection con = null;
            DbCommand cmd = null;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                cmd.CommandText = this.updateCmdText;
                num = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd);
            }
            return num;
        }

        public virtual int update(ScrumObject sObj)
        {
            int num = 0;
            DbConnection con = null;
            DbCommand cmd = null;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                if (Util.isEmpty(this.updateCmdText))
                {
                    this.genUpdateCmdText(sObj);
                }
                cmd.CommandText = this.updateCmdText;
                num = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd);
            }
            return num;
        }

        public virtual int delete(ArrayList sObjList)
        {
            int num = 0;
            DbConnection con = null;
            DbCommand cmd = null;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                cmd.CommandText = this.deleteCmdText;
                num = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd);
            }
            return num;
        }

        public int delete(ScrumObject sObj)
        {
            int num = 0;
            DbConnection con = null;
            DbCommand cmd = null;
            try
            {
                con = DatabaseManager.getDbConnection();
                cmd = con.CreateCommand();
                if (Util.isEmpty(this.deleteCmdText))
                {
                    this.genDeleteCmdText(sObj);
                }
                cmd.CommandText = this.deleteCmdText;
                num = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DatabaseManager.closeResource(con, cmd);
            }
            return num;
        }

        public void genReadCmdText()
        {
            this.readCmdText = "select * from " + this.tableName;
        }

        public virtual void genCreateCmdText(ScrumObject sObj)
        {
            this.createCmdText = "insert into " + this.tableName + " ( ";
            foreach (ScrumField sField in sObj.fieldList)
            {
                if (String.Equals(sField.name, "objecttype"))
                {
                    continue;
                }
                this.createCmdText += "" + sField.name + ",";
            }
            this.createCmdText = this.createCmdText.Substring(0, this.createCmdText.Length - 1);
            this.createCmdText += ") values (";
            foreach (ScrumField sField in sObj.fieldList)
            {
                if (String.Equals(sField.name, "objecttype"))
                {
                    continue;
                }
                this.createCmdText += "?,";

            }
            this.createCmdText = this.createCmdText.Substring(0, this.createCmdText.Length - 1);
            this.createCmdText += ")";
        }

        public virtual void genUpdateCmdText(ScrumObject sObj)
        {
            this.updateCmdText = "update " + this.tableName + " set ";
            foreach (ScrumField sField in sObj.fieldList)
            {
                if (String.Equals(sField.name, "objecttype")
                    || String.Equals(sField.name, "id__c"))
                {
                    continue;
                }
                this.updateCmdText += sField.name + "=?,";

            }
            this.updateCmdText = this.updateCmdText.Substring(0, this.updateCmdText.Length - 1);
            this.updateCmdText += " where " + ScrumField.fieldName_Id + "=" + ScrumObjectHelper.getScrumFieldValue(sObj, ScrumField.fieldName_Id);
        }

        public virtual void genDeleteCmdText(ScrumObject sObj)
        {
            this.deleteCmdText = "delete from " + this.tableName
                 + "where " + ScrumField.fieldName_Id + " = " + ScrumObjectHelper.getScrumFieldValue(sObj, ScrumField.fieldName_Id);
        }

        public void addField(ScrumObject sObj, ScrumField sField)
        {
            sObj.fieldList.Add(sField);
            sObj.fieldMap.Add(sField.name, sField);
        }
    }
}